package com.xiyu.service.repository.mailbox.mail;

import com.xiyu.service.enums.mailbox.mail.MailboxMailResourceTypeEnum;
import com.xiyu.service.enums.mailbox.mail.MailboxMailStatusEnum;
import com.xiyu.service.model.mailbox.folder.MailboxFolderFetcher;
import com.xiyu.service.model.mailbox.mail.*;
import com.xiyu.service.vo.mailbox.mail.MailPageMailInput;
import com.xiyu.service.vo.statistics.statisticsMail.StatisticsMailReceiveSendQueryInput;
import org.babyfish.jimmer.Page;
import org.babyfish.jimmer.spring.repository.JRepository;
import org.babyfish.jimmer.sql.ast.Expression;
import org.babyfish.jimmer.sql.ast.Predicate;
import org.babyfish.jimmer.sql.ast.tuple.Tuple3;
import org.babyfish.jimmer.sql.ast.tuple.Tuple5;
import org.springframework.util.StringUtils;

import java.time.LocalDateTime;
import java.util.Arrays;
import java.util.Optional;
import java.util.List;

public interface MailboxMailRepository extends JRepository<MailboxMail, Long> {
    MailboxMailTable mailboxMailTable = MailboxMailTable.$;

    default Long unreadTopCount(List<Long> accountIds){
        return sql().createQuery(mailboxMailTable)
                .where(mailboxMailTable.id().in(accountIds))
                .where(mailboxMailTable.isRead().eq(false))
                .where(mailboxMailTable.isTop().eq(true))
                .select(mailboxMailTable.id()).fetchUnlimitedCount();
    }

    default Page<MailboxMail> getMailByFolderId(MailPageMailInput inputVO, int type, List<Long> accountIds, List<Long> folderIds, List<Long> labelFolderIds){
        return sql().createQuery(mailboxMailTable)
                .where(
                        switch (type) {
                            case 0 -> mailboxMailTable.folderId().in(folderIds);
                            case 1 -> Predicate.and(
                                    mailboxMailTable.labels(label -> label.labelFolderId().in(labelFolderIds)),
                                    mailboxMailTable.accountId().in(accountIds)
                            );
                            case 2 -> mailboxMailTable.isRead().eq(false);
                            case 3 -> mailboxMailTable.isTop().eq(true);
                            case 4 -> mailboxMailTable.accountId().in(accountIds);
                            default -> null;
                        }

                )
                .where(mailboxMailTable.subject().likeIf(inputVO.getSubject()))
                .whereIf(inputVO.getLabelNames() != null, mailboxMailTable.labels(
                        label -> Predicate.and(
                                label.labelFolder().name().in(inputVO.getLabelNames()),
                                switch (type) {
                                    case 0 -> label.mail().folderId().in(folderIds);
                                    case 1, 4 -> label.mail().accountId().in(accountIds);
                                    case 2 -> label.mail().isRead().eq(false);
                                    case 3 -> label.mail().isTop().eq(true);
                                    default -> null;
                                }

                        )
                ))
                .whereIf(StringUtils.hasText(inputVO.getFileName()), mailboxMailTable.resources(
                        resource-> Predicate.and(
                                resource.fileName().like(inputVO.getFileName()),
                                switch (type) {
                                    case 0 -> resource.mail().folderId().in(folderIds);
                                    case 1 -> Predicate.and(
                                            resource.mail().labels(label -> label.labelFolderId().in(labelFolderIds)),
                                            resource.mail().accountId().in(accountIds)
                                    );
                                    case 2 -> resource.mail().isRead().eq(false);
                                    case 3 -> resource.mail().isTop().eq(true);
                                    case 4 -> resource.mail().accountId().in(accountIds);
                                    default -> null;
                                }
                        )
                ))
                .whereIf(StringUtils.hasText(inputVO.getFromName()), mailboxMailTable.froms(
                        from -> Predicate.and(
                                    Predicate.or(from.address().like(inputVO.getFromName()),
                                        from.name().like(inputVO.getFromName()),
                                        from.mail().senderName().like(inputVO.getFromName()),
                                        from.mail().senderAddress().like(inputVO.getFromName())
                                    ),
                                switch (type) {
                                    case 0 -> from.mail().folderId().in(folderIds);
                                    case 1 -> Predicate.and(
                                            from.mail().labels(label -> label.labelFolderId().in(labelFolderIds)),
                                            from.mail().accountId().in(accountIds)
                                    );
                                    case 2 -> from.mail().isRead().eq(false);
                                    case 3 -> from.mail().isTop().eq(true);
                                    case 4 -> from.mail().accountId().in(accountIds);
                                    default -> null;
                                }
                        ))
                )
                .whereIf(StringUtils.hasText(inputVO.getReceiveName()),
                        mailboxMailTable.allReceivers(
                                receiver-> Predicate.and(
                                        Predicate.or(receiver.address().like(inputVO.getReceiveName()), receiver.name().like(inputVO.getReceiveName())),
                                        receiver.type().in(Arrays.asList(0, 1)),
                                        switch (type) {
                                            case 0 -> receiver.mail().folderId().in(folderIds);
                                            case 1 -> Predicate.and(
                                                    receiver.mail().labels(label -> label.labelFolderId().in(labelFolderIds)),
                                                    receiver.mail().accountId().in(accountIds)
                                            );
                                            case 2 -> receiver.mail().isRead().eq(false);
                                            case 3 -> receiver.mail().isTop().eq(true);
                                            case 4 -> receiver.mail().accountId().in(accountIds);
                                            default -> null;
                                        }
                                )
                        ))
                .where(mailboxMailTable.content().searchContent().likeIf(inputVO.getSearchContent()))
                .whereIf(inputVO.getSendTime() != null, () -> mailboxMailTable.sendTime().between(inputVO.getSendTime()[0], inputVO.getSendTime()[1]))
                .orderBy(mailboxMailTable.isTop().desc())
                .orderBy(mailboxMailTable.sendTime().desc())
                .orderBy(mailboxMailTable.id().desc())
                .select(mailboxMailTable.fetch(MailboxMailFetcher.$.allTableFields()
                        .labels(MailboxMailLabelFetcher.$.labelFolder(MailboxFolderFetcher.$.name().color()))
                        .froms(MailboxMailFromFetcher.$.name()))
                ).fetchPage(inputVO.getPageNo() - 1, inputVO.getPageSize());
    }

    default Optional<MailboxMail> getMailInfoById(Long mailId){
        return sql().createQuery(mailboxMailTable)
                .where(mailboxMailTable.id().eq(mailId))
                .select(mailboxMailTable.fetch(MailboxMailFetcher.$.allTableFields()
                                        .froms(MailboxMailFromFetcher.$.allTableFields())
                                        .receivers(MailboxMailReceiverFetcher.$.allTableFields())
                                        .carbonReceivers(MailboxMailReceiverFetcher.$.allTableFields())
                                        .content(MailboxMailContentFetcher.$.allTableFields())
                                        .labels(MailboxMailLabelFetcher.$.labelFolder(MailboxFolderFetcher.$.name().color()))
                                        .resources(MailboxMailResourceFetcher.$.allTableFields(),
                                        it -> it.filter(args -> {args.where(args.getTable().resourceType().eq(MailboxMailResourceTypeEnum.ATTACHMENT.getValue()));}))
                        )
                ).fetchOptional();
    }

    default Optional<MailboxMail> getMailDetailById(Long mailId){
        return sql().createQuery(mailboxMailTable)
                .where(mailboxMailTable.id().eq(mailId))
                .select(mailboxMailTable.fetch(MailboxMailFetcher.$.allTableFields()
                                        .froms(MailboxMailFromFetcher.$.allTableFields())
                                        .receivers(MailboxMailReceiverFetcher.$.allTableFields())
                                        .carbonReceivers(MailboxMailReceiverFetcher.$.allTableFields())
                                        .blindReceivers(MailboxMailReceiverFetcher.$.allTableFields())
                                        .content(MailboxMailContentFetcher.$.allTableFields())
                                        .resources(MailboxMailResourceFetcher.$.allTableFields())
                        )
                ).fetchOptional();
    }

    default List<MailboxMail> getUnReceiveContentMailByFolderId(Long folderId){
        return sql().createQuery(mailboxMailTable)
                .where(mailboxMailTable.folderId().eq(folderId))
                .where(mailboxMailTable.isReceiveContent().eq(false))
                .orderBy(mailboxMailTable.id())
                .select(mailboxMailTable.fetch(MailboxMailFetcher.$.allTableFields()
                        .froms(MailboxMailFromFetcher.$.allTableFields())
                        .receivers(MailboxMailReceiverFetcher.$.allTableFields())
                        .carbonReceivers(MailboxMailReceiverFetcher.$.allTableFields())
                        .content(MailboxMailContentFetcher.$.allTableFields())
                        .resources(MailboxMailResourceFetcher.$.allTableFields(),
                                it -> it.filter(args -> {args.where(args.getTable().resourceType().eq(MailboxMailResourceTypeEnum.ATTACHMENT.getValue()));}))
                ))
                .execute();
    }

    default List<Long> getUnreadMailByFolderId(Long folderId){
        return sql().createQuery(mailboxMailTable)
                .where(mailboxMailTable.folderId().eq(folderId))
                .where(mailboxMailTable.isRead().eq(false))
                .orderBy(mailboxMailTable.id())
                .select(mailboxMailTable.id())
                .execute();
    }

    default void updateMailRead(List<Long> ids, Boolean isRead){
        sql().createUpdate(mailboxMailTable)
                .set(mailboxMailTable.isRead(), isRead)
                .where(mailboxMailTable.id().in(ids))
                .execute();
    }

    default void updateMailTop(List<Long> ids, Boolean isTop){
        sql().createUpdate(mailboxMailTable)
                .set(mailboxMailTable.isTop(), isTop)
                .where(mailboxMailTable.id().in(ids))
                .execute();
    }

    default void updateMailFolderId(Long id, Long folderId, Long beforeJunkFolderId, String uid){
        sql().createUpdate(mailboxMailTable)
                .set(mailboxMailTable.folderId(), folderId)
                .set(mailboxMailTable.beforeJunkFolderId(), beforeJunkFolderId)
                .set(mailboxMailTable.uid(), uid)
                .where(mailboxMailTable.id().eq(id))
                .execute();
    }

    default List<MailboxMail> findByIdLock(List<Long> ids){
        return sql().createQuery(mailboxMailTable)
                .where(mailboxMailTable.id().in(ids))
                .orderBy(mailboxMailTable.id())
                .select(mailboxMailTable).forUpdate().execute();
    }

    default void updateMailIsReadAndIsTop(Long mailId, boolean isRead, boolean isTop){
        sql().createUpdate(mailboxMailTable)
                .where(mailboxMailTable.id().eq(mailId))
                .set(mailboxMailTable.isTop(), isTop)
                .set(mailboxMailTable.isRead(), isRead)
                .execute();
    }

    default void updateMailContent(Long mailId, String content, String searchContent, boolean isAttachment){
        sql().createUpdate(mailboxMailTable)
                .where(mailboxMailTable.id().eq(mailId))
                .set(mailboxMailTable.content().content(), content)
                .set(mailboxMailTable.content().searchContent(), searchContent)
                .set(mailboxMailTable.isReceiveContent(), true)
                .set(mailboxMailTable.isAttachment(), isAttachment)
                .execute();
    }

    default void updateMailCalendar(Long mailId, String calendar){
        sql().createUpdate(mailboxMailTable)
                .where(mailboxMailTable.id().eq(mailId))
                .set(mailboxMailTable.calendar(), calendar)
                .execute();
    }

    default void updateMailInfo(Long mailId, String uid, int size, String messageId, LocalDateTime sendTime, LocalDateTime receiveTime){
        sql().createUpdate(mailboxMailTable)
                .where(mailboxMailTable.id().eq(mailId))
                .set(mailboxMailTable.uid(), uid)
                .set(mailboxMailTable.size(), Long.valueOf(size))
                .set(mailboxMailTable.messageId(), messageId)
                .set(mailboxMailTable.sendTime(), sendTime)
                .set(mailboxMailTable.receiveTime(), receiveTime)
                .execute();
    }


    default Optional<Long> findFirstByFolderIdAndUid(Long folderId, String uniqueCode){
        return sql().createQuery(mailboxMailTable)
                .where(mailboxMailTable.folderId().eq(folderId))
                .where(mailboxMailTable.uid().eq(uniqueCode))
                .select(mailboxMailTable.id())
                .forUpdate()
                .fetchOptional();
    }

    default Long findTopMailCount(Long folderId){
        return sql().createQuery(mailboxMailTable)
                .where(mailboxMailTable.folderId().eq(folderId))
                .where(mailboxMailTable.isTop().eq(true))
                .select(mailboxMailTable.id())
                .fetchUnlimitedCount();
    }

    default Tuple3<Long, Long, Long> groupCountByFolderId(Long folderId){
        return sql().createQuery(mailboxMailTable)
                .where(mailboxMailTable.folderId().eq(folderId))
                .select(mailboxMailTable.id().count(),
                        Expression.any().sql(Long.class,"IFNULL(SUM(IF(%e, 1, 0)),0)",mailboxMailTable.isRead()),
                        Expression.any().sql(Long.class,"IFNULL(SUM(IF(%e, 1, 0)),0)",mailboxMailTable.isTop()))
                .fetchOptional().orElse(null);
    }

    default List<Tuple5<Long, LocalDateTime, Long, Long, Long>> receiveSendQuery(List<Long> userOwnerId, StatisticsMailReceiveSendQueryInput inputVO){
        return sql().createQuery(mailboxMailTable)
                .where(mailboxMailTable.account().ownerId().in(userOwnerId))
                .whereIf(!inputVO.getOwnerIds().isEmpty(), mailboxMailTable.account().ownerId().in(inputVO.getOwnerIds()))
                .where(mailboxMailTable.sendTime().between(inputVO.getStatisticsTime()[0], inputVO.getStatisticsTime()[1]))
                .groupBy(mailboxMailTable.account().ownerId(), Expression.any().sql(LocalDateTime.class, "DATE_FORMAT(%e, '%Y-%m-%d')", mailboxMailTable.sendTime()))
                .orderBy(mailboxMailTable.sendTime())
                .select(mailboxMailTable.account().ownerId(), Expression.any().sql(LocalDateTime.class, "DATE_FORMAT(%e, '%Y-%m-%d')", mailboxMailTable.sendTime()),
                        Expression.any().sql(Long.class,"IFNULL(SUM(IF(%e = %v, 1, 0)),0)", mailboxMailTable.status(), MailboxMailStatusEnum.RECEIVED.getValue()),
                        Expression.any().sql(Long.class,"IFNULL(SUM(IF(%e = %v, 1, 0)),0)", mailboxMailTable.status(), MailboxMailStatusEnum.SEND.getValue()),
                        Expression.any().sql(Long.class,"IFNULL(SUM(IF(%e = 1 && %e = %v, 1, 0)),0)", new Expression[] {  mailboxMailTable.receiverCount(), mailboxMailTable.status() } ,
                                MailboxMailStatusEnum.SEND.getValue() )
                )
                .execute();
    }

    default List<Long> findScheduleMail(Long accountId){
        return sql().createQuery(mailboxMailTable)
                .where(mailboxMailTable.accountId().eq(accountId))
                .where(mailboxMailTable.status().eq(MailboxMailStatusEnum.DRAFT.getValue()))
                .where(mailboxMailTable.isScheduled().eq(true))
                .where(mailboxMailTable.planSendTime().le(LocalDateTime.now()))
                .select(mailboxMailTable.id())
                .execute();
    }

    List<MailboxMail> findByAccountId(Long accountId);

}